/*
	exec sp_missed_quarter_report '0','0','0','0','0','0','0','0','0',1
*/
ALTER             proc sp_missed_quarter_report (
@from_date varchar(20),
@to_date varchar(20),
@c_id varchar(10),
@pt_id varchar(10),
@region varchar(50),
@rep_office varchar(50),
@end_user varchar(50),
@sales_person varchar(50),
@default_prompt varchar(20),
@order_type int)


as
	begin

		declare @results Table(So_No varchar(50),SalesOrderID int, LineItemNo int,CoolSchdDate datetime,ActSchdDate datetime,Status varchar(20),ProductID int, PartName varchar(50), CustomerID int, CustomerName varchar(50),SODate datetime,ReqDockDate datetime, ReqQty int,QtyShipped int, UnitCost float,Region varchar(50), RepOffice varchar(50),EndUser varchar(50),SalesPerson varchar(50),Qtr int, Yr int, Mth int,ChangeDate datetime, BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,EndCustomer varchar(50))
		declare @so_id int, @line_item_no int, @qty_shipped int, @act_qty int
		declare @so_no varchar(50),  @product_id int,@cust_id int
		declare @cool_schd_date datetime,@act_date datetime, @so_date datetime,@cool_req_date datetime
		declare @unit_price float
		declare @status varchar(20),@part_no varchar(50), @cust_name varchar(50),@cust_region varchar(50),@so_rep_office varchar(50), @so_end_user varchar(50),@so_sales_person varchar(50)	
		declare @month int, @quarter int, @year int
		declare @time_stamp datetime

		declare @change_date datetime -- Added by Basha (11/20/2003)
		declare @so_BK_Mt_I int, @so_Bk_Yr_I int, @so_Bk_Qt_I int, @so_Bk_Qt_End_I int -- Added by Basha (11/21/2003)
		declare @EndCustomer varchar(50)

		set @time_stamp = (select getDate())
		declare mainCursor cursor for 
		select * from fn_missed_orders_report( @time_stamp, @order_type )
		open mainCursor
		fetch next from mainCursor into @so_no, @so_id, @line_item_no,@cool_schd_date,@act_date,
				@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,
				@act_qty, @qty_shipped, @unit_price, @cust_region, @so_rep_office, 
				@so_end_user,@so_sales_person,@change_date,@so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer
		while @@fetch_status = 0
			begin
				if (@qty_shipped is null)
			begin
				set @qty_shipped = 0
			end
		
				set @month = DATEPART(m,@cool_schd_date)
				
				set @year=DATEPART(yyyy,@cool_schd_date)
				
				if  @month >=1 and @month <=3
				begin
					set @quarter = 1
					set @month = 3
				
					insert into @results values(@so_no, @so_id, @line_item_no,@cool_schd_date,@act_date,
	   				@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,
					@act_qty, @qty_shipped, @unit_price, @cust_region, @so_rep_office, 
					@so_end_user,@so_sales_person, @quarter,@year,@month,@change_date,@so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
				end
				
				else if  @month >=4 and @month <=6
				begin
					set @quarter = 2
					set @month = 6
							
					insert into @results values(@so_no, @so_id, @line_item_no,@cool_schd_date,@act_date,
	   				@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,
					@act_qty, @qty_shipped, @unit_price, @cust_region, @so_rep_office, 
					@so_end_user,@so_sales_person, @quarter,@year,@month,@change_date,@so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
					
				end
				else if  @month >=7 and @month <=9
				begin
					
					set @quarter = 3
					set @month = 9
				
				insert into @results values(@so_no, @so_id, @line_item_no,@cool_schd_date,@act_date,
	   				@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,
					@act_qty, @qty_shipped, @unit_price, @cust_region, @so_rep_office, 
					@so_end_user,@so_sales_person, @quarter,@year,@month,@change_date,@so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
				end
				
				else if  @month >=10 or @month <= 12
				begin
					set @quarter = 4
					set @month = 12
									 					
					insert into @results values(@so_no, @so_id, @line_item_no,@cool_schd_date,@act_date,
	   				@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,
					@act_qty, @qty_shipped, @unit_price, @cust_region, @so_rep_office, 
					@so_end_user,@so_sales_person, @quarter,@year,@month,@change_date,@so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
				end
				
			set @act_date=null
			set @part_no=null
			set @cust_name=null
			set @cool_req_date=null
			set @act_qty=null
			set @qty_shipped=null
			set @unit_price=null
			set @cust_region=null
			set @so_rep_office=null
			set @so_end_user=null
			set @so_sales_person=null
			set @EndCustomer = null

			fetch next from mainCursor into @so_no, @so_id, @line_item_no,@cool_schd_date,@act_date,
		   		@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,
				@act_qty, @qty_shipped, @unit_price, @cust_region, @so_rep_office, 
				@so_end_user,@so_sales_person,@change_date,@so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I, @EndCustomer
			
			end			
		close mainCursor
		deallocate mainCursor
	
end

select * from @results order by ActSchdDate	